import asyncio

import aiomysql
from loguru import logger

from com.arcfox.util import constant
from com.arcfox.util.util import sql_param_pro


async def __connect_db():
    return await aiomysql.connect(constant.DB_HOST, constant.DB_USER_NAME, constant.DB_PASSWORD, constant.DB_NAME,
                                  constant.DB_PORT, charset='utf8')


def open_and_close_db(func):
    async def wrapper(*a, **k):
        conn = await __connect_db()
        cursor = await conn.cursor()
        # logger.info(f"[{func.__name__}]打开db连接")
        try:
            if a:
                logger.info(a)
            result = await func(*a, **k, cursor=cursor)
            await conn.commit()
        finally:
            if conn:
                conn.close()
                # logger.info(f"[{func.__name__}]关闭db连接")
        return result

    return wrapper


@open_and_close_db
async def save_or_update(sql, cursor):
    await cursor.execute(sql)


@open_and_close_db
async def delete(sql, cursor):
    await cursor.execute(sql)


@open_and_close_db
async def truncate(table, cursor):
    sql = f"truncate table {table}"
    await cursor.execute(sql)


@open_and_close_db
async def query_all(sql, cursor):
    await cursor.execute(sql)
    result = await cursor.fetchall()
    return result


@open_and_close_db
async def query_one(sql, cursor):
    await cursor.execute(sql)
    result = await cursor.fetchone()
    return result


async def exist(sql):
    result = await query_all(sql)
    if result:
        return True
    return False


class MysqlMapping:

    def __init__(self, table_name):
        '''
        Mysql ORM 工具类
        :param table_name: 表名
        '''
        self.table_name = table_name

    async def query(self, condition: dict = {}, rows=[], orders=[]):
        '''
        查询
        :param condition: 查询条件 eg. {"title": "xxx", "content": "yyy"}
        :param rows: 查询的字段
        :param orders: 排序规则 eg. [{"row": "university_rank", "asc": True}]
        :return:
        '''

        assert self.table_name, "表名不能为空"
        assert isinstance(condition, dict), "condition must be dict!"
        return await query_all(self.__mapping_query_sql(self.table_name, condition, rows, orders))

    async def insert(self, mapper: dict):
        '''
        插入数据
        :param mapper: 插入的数据映射 eg. {"title": "xxx", "content": "yyy"}
        :return:
        '''
        assert self.table_name, "表名不能为空"
        assert isinstance(mapper, dict), "mapper must be dict!"
        await save_or_update(self.__mapping_insert_sql(self.table_name, mapper))

    async def update(self, mapper: dict, condition: dict):
        '''
        更新数据
        :param mapper: 更新的数据映射 eg. {"title": "xxx", "content": "yyy"}
        :param condition: 更新的条件映射 eg. {"id": "xxx"}
        :return:
        '''
        assert self.table_name, "表名不能为空"
        assert isinstance(mapper, dict), "mapper must be dict!"
        assert isinstance(condition, dict), "condition must be dict!"
        await save_or_update(self.__mapping_update_sql(self.table_name, mapper, condition))

    async def delete(self, condition: dict):
        '''
        删除数据
        :param condition: 删除条件 eg. {"id": "xxx"}
        :return:
        '''
        assert self.table_name, "表名不能为空"
        assert isinstance(condition, dict), "condition must be dict!"
        sql = self.__mapping_delete_sql(self.table_name, condition)
        await delete(sql)

    def __mapping_query_sql(self, table_name, condition, rows=[], orders=[]):
        query_rows = ",".join(rows) if rows else "*"
        query_condition = ""
        for key in condition.keys():
            value = self.__get_format_sql_value(condition[key])
            query_condition += f"{key} = {value} and "
        sql = f"select {query_rows} from {table_name} where 1=1 "
        if query_condition:
            sql += f"and {query_condition[:-5]} "
        if orders:
            order_condition = f"order by "
            for order in orders:
                order_condition += f"{order['row']} asc," if order['asc'] else f"{order['row']} desc,"
            sql += order_condition[:-1]
        return sql

    def __mapping_insert_sql(self, table_name, mapper):
        columns = ""
        data_set = ""
        for key in mapper.keys():
            if isinstance(mapper[key], int) or mapper[key]:
                columns += f"{key},"
                value = self.__get_format_sql_value(mapper[key])
                data_set += value + ","
        sql = f"insert into {table_name} ({columns[:-1]}) values ({data_set[:-1]})"
        return sql

    def __mapping_update_sql(self, table_name, mapper, condition):
        update_row = ""
        update_condition = ""
        for key in mapper.keys():
            value = self.__get_format_sql_value(mapper[key])
            update_row += f"{key} = {value}, "
        for key in condition.keys():
            value = self.__get_format_sql_value(condition[key])
            update_condition += f"{key} = {value} and "
        sql = f"update {table_name} set {update_row[:-2]} where {update_condition[:-5]}"
        return sql

    def __mapping_delete_sql(self, table_name, condition):
        update_condition = ""
        for key in condition.keys():
            value = self.__get_format_sql_value(condition[key])
            update_condition += f"{key} = {value} and "
        sql = f"delete from {table_name}  where {update_condition[:-5]}"
        return sql

    @staticmethod
    def __get_format_sql_value(value):
        if isinstance(value, int) or isinstance(value, float):
            value = f"{value}"
        elif isinstance(value, str) and value.lower() == "now()":
            value = f"{value}"
        else:
            if not value:
                value = "\'\'"
            else:
                value = f"'{sql_param_pro(value)}'"
        return value


if __name__ == "__main__":
    print(isinstance(None, int))
    # task = {'university_uid': '444914843d151ed533fd7ceb79693577', 'university_name': '上海外国语大学', 'university_department': '俄罗斯东欧中亚学院', 'major_name': '乌兹别克语', 'major_code': '050258', 'major_degree': '本', 'is_province_point': 0, 'is_nation_point': 0, 'main_major': 0}
    # asyncio.run(MysqlMapping("tb_test").update(task, {"id": 102348}))
